<?php

namespace App\Utils;
class Connector
{

    /**
     * 数据库配置信息
     */
    private $config = null;

    /**
     * 数据库连接资源句柄
     */
    public $link = null;

    /**
     * 最近一次查询资源句柄
     */
    public \mysqli_result|null|bool $lastqueryid = null;

    /**
     *  统计数据库查询次数
     */
    public $querycount = 0;

    public function __construct()
    {
    }

    /**
     * 打开数据库连接,有可能不真实连接数据库
     * @param $config
     * @return void
     */
    public function open($config)
    {
        $this->config = $config;
        if ($config['autoconnect'] == 1) {
            $this->connect();
        }
    }

    public function setLink($link) {
        $this->link = $link;
    }

    public function setConfig($config) {
        $this->config = $config;
    }

    /**
     * 真正开启数据库连接
     *
     * @return void
     */
    public function connect()
    {
        if ($this->link && $this->link->ping()) {
            return $this->link;
        }
        $this->link = new \mysqli($this->config['hostname'], $this->config['username'], $this->config['password'], $this->config['database'], $this->config['port'] ? intval($this->config['port']) : 3306);
        if (mysqli_connect_error()) {
            $this->halt('Can not connect to MySQL server');
            return false;
        }

        if ($this->version() > '4.1') {
            $charset = isset($this->config['charset']) ? $this->config['charset'] : '';
            $serverset = $charset ? "character_set_connection='$charset',character_set_results='$charset',character_set_client=binary" : '';
            $serverset .= $this->version() > '5.0.1' ? ((empty($serverset) ? '' : ',') . " sql_mode='' ") : '';
            $serverset && $this->link->query("SET $serverset");
        }
        return $this->link;
    }

    /**
     * 数据库查询执行方法
     * @param $sql
     * @return mixed|null
     */
    private function execute($sql): mixed
    {
        if (!is_object($this->link)) {
            $this->connect();
        }
        if (str_contains($sql, 'tablepre_')) {
            $sql = str_replace('tablepre_', $this->config['tablepre'], $sql);
        }
        $this->lastqueryid = $this->link->query($sql) or $this->halt($this->link->error, $sql);
        $this->querycount++;
        return $this->lastqueryid;
    }

    /**
     * 查询列表
     * @param $table
     * @param $data
     * @param $where
     * @param $limit
     * @param $order
     * @param $group
     * @param $key
     * @return array|null
     */
    public function select($table, $data = '*', $where = '', $limit = '', $order = '', $group = '', $key = ''): ?array
    {
        $where = $this->_parse_where($where);
        $table = $this->_parse_table($table);
        $where = $where == '' ? '' : ' WHERE ' . $where;
        $order = $order == '' ? '' : ' ORDER BY ' . $order;
        $group = $group == '' ? '' : ' GROUP BY ' . $group;
        $limit = $limit == '' ? '' : ' LIMIT ' . $limit;
        if ($data != '*') {
            $field = explode(',', $data);
            array_walk($field, array($this, 'add_special_char'));
            $data = implode(',', $field);
        }
        $sql = 'SELECT ' . $data . ' FROM `' . $this->config['database'] . '`.`' . $table . '`' . $where . $group . $order . $limit;
        $this->execute($sql);
        if (!is_object($this->lastqueryid)) {
            return $this->lastqueryid;
        }

        $datalist = array();
        while (($rs = $this->fetch_next()) != false) {
            if ($key) {
                $datalist[$rs[$key]] = $rs;
            } else {
                $datalist[] = $rs;
            }
        }
        $this->free_result();
        return $datalist;
    }

    /**
     * 获取单条信息
     * @param $table
     * @param string $data
     * @param $where
     * @param $order
     * @param $group
     * @return mixed
     */
    public function get_one($table, string $data = '*', $where = '', $order = '', $group = ''): mixed
    {
        $where = $this->_parse_where($where);
        $table = $this->_parse_table($table);
        $where = $where == '' ? '' : ' WHERE ' . $where;
        $order = $order == '' ? '' : ' ORDER BY ' . $order;
        $group = $group == '' ? '' : ' GROUP BY ' . $group;
        $limit = ' LIMIT 1';
        if ($data != '*') {
            $field = explode(',', $data);
            array_walk($field, array($this, 'add_special_char'));
            $data = implode(',', $field);
        }
        $sql = 'SELECT ' . $data . ' FROM `' . $this->config['database'] . '`.`' . $table . '`' . $where . $group . $order . $limit;
        $this->execute($sql);
        $res = $this->fetch_next();
        $this->free_result();
        return $res;
    }

    /**
     * 遍历查询结果集
     * @param $type MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH
     * @return mixed
     */
    public function fetch_next($type = MYSQLI_ASSOC): mixed
    {
        $res = $this->lastqueryid->fetch_array($type);
        if (!$res) {
            $this->free_result();
        }
        return $res;
    }

    /**
     * 释放查询资源
     * @return void
     */
    public function free_result(): void
    {
        if (is_resource($this->lastqueryid)) {
            $this->lastqueryid->free();
            $this->lastqueryid = null;
        }
    }

    /**
     * 直接执行sql查询
     * @param $sql
     * @return mixed|null
     */
    public function query($sql)
    {
        return $this->execute($sql);
    }

    /**
     * 插入
     * @param $table
     * @param $data
     * @param $return_insert_id
     * @param $replace
     * @return false|int|mixed|null
     */
    public function insert($table, $data, $return_insert_id = false, $replace = false)
    {
        if (!is_array($data) || $table == '' || count($data) == 0) {
            return false;
        }
        $table = $this->_parse_table($table);
        $valuedata = array_values($data);
        if (is_array($valuedata[0])) {
            $fielddata = array_keys($valuedata[0]);
        } else {
            $fielddata = array_keys($data);
            $valuedata = array($data);
        }
        array_walk($fielddata, array($this, 'add_special_char'));
        $values = '';
        foreach ($valuedata as $vdata) {
            $vdata = array_values($vdata);
            array_walk($vdata, array($this, 'escape_string'));
            $values .= ',(' . implode(',', $vdata) . ')';
        }

        $field = implode(',', $fielddata);

        $cmd = $replace ? 'REPLACE INTO' : 'INSERT INTO';
        $sql = $cmd . ' `' . $this->config['database'] . '`.`' . $table . '`(' . $field . ') VALUES ' . substr($values, 1);
        $return = $this->execute($sql);
        return $return_insert_id ? $this->insert_id() : $return;
    }

    /**
     * 获取最后一次添加记录的主键号
     * @return int
     */
    public function insert_id(): int
    {
        if (!is_object($this->link)) {
            $this->connect();
        }
        return $this->link->insert_id;
    }

    /**
     * 执行更新记录操作
     * @param $table
     * @param $data
     * @param $where
     * @return false|mixed|null
     */
    public function update($table, $data, $where = '')
    {
        if ($table == '' or $where == '') {
            return false;
        }
        $where = $this->_parse_where($where);
        $table = $this->_parse_table($table);

        $where = ' WHERE ' . $where;
        $field = '';
        if (is_string($data) && $data != '') {
            $field = $data;
        } elseif (is_array($data) && count($data) > 0) {
            $fields = array();
            foreach ($data as $k => $v) {
                switch (substr($v, 0, 2)) {
                    case '+=':
                        $v = substr($v, 2);
                        if (is_numeric($v)) {
                            $fields[] = $this->add_special_char($k) . '=' . $this->add_special_char($k) . '+' . $this->escape_string($v, '', false);
                        } else {
                            continue 2;
                        }

                        break;
                    case '-=':
                        $v = substr($v, 2);
                        if (is_numeric($v)) {
                            $fields[] = $this->add_special_char($k) . '=' . $this->add_special_char($k) . '-' . $this->escape_string($v, '', false);
                        } else {
                            continue 2;
                        }
                        break;
                    default:
                        $fields[] = $this->add_special_char($k) . '=' . $this->escape_string($v);
                }
            }
            $field = implode(',', $fields);
        } else {
            return false;
        }

        $sql = 'UPDATE `' . $this->config['database'] . '`.`' . $table . '` SET ' . $field . $where;
        return $this->execute($sql);
    }

    /**
     * 执行删除记录操作
     * @param $table
     * @param $where
     * @return false|mixed|null
     */
    public function delete($table, $where)
    {
        if ($table == '' || $where == '') {
            return false;
        }
        $where = $this->_parse_where($where);
        $table = $this->_parse_table($table);
        $where = ' WHERE ' . $where;
        $sql = 'DELETE FROM `' . $this->config['database'] . '`.`' . $table . '`' . $where;
        return $this->execute($sql);
    }

    /**
     * 获取最后数据库操作影响到的条数
     * @return int
     */
    public function affected_rows()
    {
        if (!is_object($this->link)) {
            $this->connect();
        }
        return $this->link->affected_rows;
    }

    /**
     * 获取数据表主键
     * @param $table
     * @return mixed
     */
    public function get_primary($table)
    {
        $table = $this->_parse_table($table);
        $this->execute("SHOW COLUMNS FROM $table");
        while ($r = $this->fetch_next()) {
            if ($r['Key'] == 'PRI') {
                break;
            }
        }
        return $r['Field'];
    }

    /**
     * 获取表字段
     * @param $table
     * @return array
     */
    public function get_fields($table)
    {
        $table = $this->_parse_table($table);
        $fields = array();
        $this->execute("SHOW COLUMNS FROM $table");
        while ($r = $this->fetch_next()) {
            $fields[$r['Field']] = $r['Type'];
        }
        return $fields;
    }

    /**
     * 检查不存在的字段
     * @param $table
     * @param $array
     * @return array
     */
    public function check_fields($table, $array)
    {
        $table = $this->_parse_table($table);
        $fields = $this->get_fields($table);
        $nofields = array();
        foreach ($array as $v) {
            if (!array_key_exists($v, $fields)) {
                $nofields[] = $v;
            }
        }
        return $nofields;
    }

    /**
     * 检查表是否存在
     * @param $table
     * @return int
     */
    public function table_exists($table)
    {
        $table = $this->_parse_table($table);
        $tables = $this->list_tables();
        return in_array($table, $tables) ? 1 : 0;
    }

    /**
     * 表列表
     * @return array
     */
    public function list_tables()
    {
        $tables = array();
        $this->execute("SHOW TABLES");
        while ($r = $this->fetch_next()) {
            $tables[] = $r['Tables_in_' . $this->config['database']];
        }
        return $tables;
    }

    /**
     * 检查字段是否存在
     * @param $table
     * @param $field
     * @return bool
     */
    public function field_exists($table, $field)
    {
        $table = $this->_parse_table($table);
        $fields = $this->get_fields($table);
        return array_key_exists($field, $fields);
    }

    public function num_rows($sql)
    {
        $this->lastqueryid = $this->execute($sql);
        return $this->lastqueryid ? $this->lastqueryid->num_rows : 0;
    }

    public function num_fields($sql)
    {
        $this->lastqueryid = $this->execute($sql);
        return $this->lastqueryid ? $this->lastqueryid->field_count : null;
    }

    public function result($sql, $row)
    {
        $this->lastqueryid = $this->execute($sql);
        $this->lastqueryid->data_seek($row);
        $assocs = $this->lastqueryid->fetch_row();
        return $assocs[0];
    }

    public function error()
    {
        if (!is_object($this->link)) {
            $this->connect();
        }
        return $this->link->error;
    }

    public function errno()
    {
        if (!is_object($this->link)) {
            $this->connect();
        }
        return intval($this->link->errno);
    }

    public function version()
    {
        if (!is_object($this->link)) {
            $this->connect();
        }
        return $this->link->server_info; //server_version
    }

    public function close()
    {
        if ($this->link) {
            $this->link->close();
        }
        $this->link = null;
    }

    public function escape($str)
    {
        if (!is_object($this->link)) {
            $this->connect();
        }
        return $this->link->real_escape_string($str);
    }

    public function halt($message = '', $sql = '')
    {
        if ($this->config['debug']) {
            $this->errormsg = "<b>MySQL Query : </b> $sql <br /><b> MySQL Error : </b>" . $this->error() . " <br /> <b>MySQL Errno : </b>" . $this->errno() . " <br /><b> Message : </b> $message ";
            $msg = $this->errormsg;
            echo '<div style="font-size:12px;text-align:left; border:1px solid #9cc9e0; padding:1px 4px;color:#000000;font-family:Arial, Helvetica,sans-serif;"><span>' . $msg . '</span></div>';
            exit;
        } else {
            return false;
        }
    }

    /**
     * 对字段两边加反引号，以保证数据库安全
     * @param $value 数组值
     */
    public function add_special_char(&$value)
    {
        if ('*' == $value || false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos($value, '`')) {
            //不处理包含* 或者 使用了sql方法。
        } else {
            $value = '`' . trim($value) . '`';
        }
        if (preg_match("/\b(select|insert|update|delete)\b/i", $value)) {
            $value = preg_replace("/\b(select|insert|update|delete)\b/i", '', $value);
        }
        return $value;
    }

    /**
     * 对字段值两边加引号，以保证数据库安全
     * @param $value
     * @param $key
     * @param $quotation
     * @return string
     */
    public function escape_string(&$value, $key = '', $quotation = 1)
    {
        if ($quotation) {
            $q = '\'';
        } else {
            $q = '';
        }
        $value = $q . $value . $q;
        return $value;
    }

    /**
     * 开启事务
     * @return void
     */
    public function begin()
    {
        $this->link->autocommit(false);
    }

    /**
     * 事务回滚
     * @return void
     */
    public function rollback()
    {
        $this->link->rollback();
        $this->link->autocommit(true);
    }

    /**
     * 事务提交
     * @return void
     */
    public function commit()
    {
        $this->link->commit();
        $this->link->autocommit(true);
    }

    private function _parse_where($where, $font = ' AND ')
    {
        if (is_array($where)) {
            $sql = '';
            foreach ($where as $key => $val) {
                if (is_array($val)) {
                    if (count($val) == 2 && in_array($val[0], ['neq', 'lt', 'lte', 'gt', 'gte', 'like'])) {
                        $sql .= ($sql ? $font : '') . "`$key` ";
                        switch ($val[0]) {
                            case 'neq':
                                $sql .= "!= {$val[1]}";
                                break;
                            case 'lt':
                                $sql .= "< {$val[1]}";
                                break;
                            case 'lte':
                                $sql .= "<= {$val[1]}";
                                break;
                            case 'gt':
                                $sql .= "> {$val[1]}";
                                break;
                            case 'gte':
                                $sql .= ">= {$val[1]}";
                                break;
                            case 'like':
                                $sql .= "LIKE '%{$val[1]}%'";
                                break;
                        }
                    } else {
                        $in = '';
                        foreach ($val as $v) {
                            $in .= ",'" . addslashes($v) . "'";
                        }
                        $sql .= $sql ? " $font `$key` IN (" . substr($in, 1) . ")" : "`$key` IN (" . substr($in, 1) . ")";
                    }
                } else {
                    if (is_int($key)) {
                        $sql .= $sql ? " $font $val " : " $val";
                    } else {
                        $sql .= $sql ? " $font `$key` = '$val' " : " `$key` = '$val'";
                    }
                }
            }
            return $sql;
        } else {
            return $where;
        }
    }

    private function _parse_table($table)
    {
        if (!str_starts_with($table, $this->config['tablepre'])) {
            $table = $this->config['tablepre'] . $table;
        }
        return $table;
    }
}
